package c0907m.group2.aitsm.core.db;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from User_Subject table.
 */
public class UserSubjectDb extends BaseDb {
    public static final int USER = 0;
    public static final int SUBJECT = 1;
    private String insert = "INSERT INTO user_subject VALUES (?, ?)";
    private String delete = "DELETE FROM user_subject WHERE user_id = ?";

    /**
     * Get a list of Subject IDs for the given User or contrary.
     *
     * @param id   the row's user_id or subject_id to search.
     * @param type type of given id. For example, if type is USER then search for subject and contrary.
     * @return list of subject_id or user_id found.
     * @throws SQLException if a database access error occurs.
     */
    public List<Integer> select(int id, int type) throws SQLException {
        String select = "";
        if (type == USER)
            select = "SELECT subject_id FROM user_subject WHERE user_id = " + id;
        if (type == SUBJECT)
            select = "SELECT user_id FROM user_subject WHERE subject_id = " + id;

        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<Integer> list = new ArrayList<Integer>();

            while (rs.next()) {
                list.add(rs.getInt(1));
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update Subjects for the given User.
     *
     * @param user_id     the course's id to update.
     * @param subject_ids list of subject_id to add.
     * @throws SQLException if a database access error occurs.
     */
    public void update(int user_id, List<Integer> subject_ids) throws SQLException {
        try {
            connect();

            st = con.prepareStatement(delete);
            delete(user_id);

            st = con.prepareStatement(insert);
            insert(user_id, subject_ids);
        } finally {
            close();
        }
    }

    /**
     * Delete all Subjects for the given User.
     *
     * @param user_id user's id to delete.
     * @throws SQLException if a database access error occurs.
     */
    private void delete(int user_id) throws SQLException {
        st.setInt(1, user_id);
        st.executeUpdate();
    }

    /**
     * Insert a list of Subjects for the given User into database.
     *
     * @param user_id     course's id to insert.
     * @param subject_ids list of subject_id to insert.
     * @throws SQLException if a database access error occurs.
     */
    private void insert(int user_id, List<Integer> subject_ids) throws SQLException {
        for (int subject_id : subject_ids) {
            st.setInt(1, user_id);
            st.setInt(2, subject_id);
            st.executeUpdate();
        }
    }
}
